package dao;

import po.Address;
import po.Book;
import po.Order;
import po.OrderItem;
import utils.DBUtils;
import vo.OrderDetailVo;
import vo.OrderItemVo;
import vo.OrderWitnVo;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;

public class OrderDao {
    public void insert(Order order){
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        String sql ="insert into tb_order (user_id,order_id,sta) values(?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,order.getUserId());
            preparedStatement.setString(2,order.getOrderId());
            preparedStatement.setString(3,order.getSta());
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
    }

    public void updateOrderById(String orderId, Integer address, Double payment, String status) {
        Connection connection = DBUtils.getConn();
        String sql ="update tb_order set address_id=?,payment=?,sta=?,placed=? where order_id=?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,address);
            preparedStatement.setDouble(2,payment);
            preparedStatement.setString(3,status);
            preparedStatement.setTimestamp(4,new Timestamp(new Date().getTime()));
            preparedStatement.setString(5, orderId);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }

    }

    public ArrayList<OrderItemVo> findAllByUser(String phone,int start,int length) {
        Connection connection = DBUtils.getConn();
        String sql ="select orders.handover,item.rid,book.price,book.isbn,book.press,book.title,item.order_id," +
                "orders.sta,orders.placed,item.price oprice,item.count,item.product from tb_book book " +
                "inner join tb_order_item item on book.isbn=item.product " +
                "inner join tb_order orders on item.order_id=orders.order_id " +
                "where orders.user_id=? order by item.order_id desc,item.rid desc limit ?,?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Order order = null;
        OrderItem orderItem = null;
        OrderItemVo orderItemVo = null;
        Book b = null;
        ArrayList<OrderItemVo> list = new ArrayList<OrderItemVo>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,phone);
            preparedStatement.setInt(2,start);
            preparedStatement.setInt(3,length);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                b = new Book();
                order = new Order();
                orderItem = new OrderItem();
                orderItemVo = new OrderItemVo();
                b.setIsbn(resultSet.getString("isbn"));
                b.setPress(resultSet.getString("press"));
                b.setTitle(resultSet.getString("title"));
                b.setPrice(resultSet.getDouble("price"));
                orderItemVo.setBook(b);
                order.setSta(resultSet.getString("sta"));
                order.setOrderId(resultSet.getString("order_id"));
                order.setPlaced(resultSet.getDate("placed"));
                order.setHandover(resultSet.getTimestamp("handover"));
                orderItemVo.setOrder(order);
                orderItem.setPrice(resultSet.getDouble("oprice"));
                orderItem.setCount(resultSet.getInt("count"));
                orderItem.setOrderId(resultSet.getString("order_id"));
                orderItem.setProduct(resultSet.getString("isbn"));
                orderItemVo.setOrderItem(orderItem);
                list.add(orderItemVo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;

    }
    public int selectCount() {
        int totalCount = 0;
        Connection connection = DBUtils.getConn();
        String sql = "select count(*) from tb_order";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                totalCount = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return totalCount;

    }

    public ArrayList<Order> selectAllPaged(int start, int length) {
        ArrayList<Order> list = new ArrayList<Order>();
        Order order = null;
        Connection connection = DBUtils.getConn();
        String sql = "select * from tb_order order by placed desc limit ?,?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,start);
            preparedStatement.setInt(2,length);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                order = new Order();
                order.setOrderId(resultSet.getString("order_id"));
                order.setAddressId(resultSet.getInt("address_id"));
                order.setRid(resultSet.getInt("rid"));
                order.setSta(resultSet.getString("sta"));
                order.setUserId(resultSet.getString("user_id"));
                order.setPayment(resultSet.getDouble("payment"));
                order.setPlaced(resultSet.getTimestamp("placed"));
                order.setReceipt(resultSet.getTimestamp("receipt"));
                order.setDeliver(resultSet.getTimestamp("deliver"));
                order.setHandover(resultSet.getTimestamp("handover"));
                list.add(order);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;

    }

    public ArrayList<Order> selectProcess(int start, int length) {
        ArrayList<Order> list = new ArrayList<Order>();
        Order order = null;
        Connection connection = DBUtils.getConn();
        String sql = "select * from tb_order where sta='待处理' order by placed desc limit ?,?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,start);
            preparedStatement.setInt(2,length);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                order = new Order();
                order.setOrderId(resultSet.getString("order_id"));
                order.setAddressId(resultSet.getInt("address_id"));
                order.setRid(resultSet.getInt("rid"));
                order.setSta(resultSet.getString("sta"));
                order.setUserId(resultSet.getString("user_id"));
                order.setPayment(resultSet.getDouble("payment"));
                order.setPlaced(resultSet.getTimestamp("placed"));
                order.setReceipt(resultSet.getTimestamp("receipt"));
                order.setDeliver(resultSet.getTimestamp("deliver"));
                order.setHandover(resultSet.getTimestamp("handover"));
                list.add(order);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }

    public ArrayList<Order> selectProcesssing(int start, int length) {
        ArrayList<Order> list = new ArrayList<Order>();
        Order order = null;
        Connection connection = DBUtils.getConn();
        String sql = "select * from tb_order where sta='处理中' order by placed desc limit ?,?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,start);
            preparedStatement.setInt(2,length);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                order = new Order();
                order.setOrderId(resultSet.getString("order_id"));
                order.setAddressId(resultSet.getInt("address_id"));
                order.setRid(resultSet.getInt("rid"));
                order.setSta(resultSet.getString("sta"));
                order.setUserId(resultSet.getString("user_id"));
                order.setPayment(resultSet.getDouble("payment"));
                order.setPlaced(resultSet.getTimestamp("placed"));
                order.setReceipt(resultSet.getTimestamp("receipt"));
                order.setDeliver(resultSet.getTimestamp("deliver"));
                order.setHandover(resultSet.getTimestamp("handover"));
                list.add(order);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }

    public ArrayList<Order> selectSent(int start, int length) {
        ArrayList<Order> list = new ArrayList<Order>();
        Order order = null;
        Connection connection = DBUtils.getConn();
        String sql = "select * from tb_order where sta='已发货' order by placed desc limit ?,?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,start);
            preparedStatement.setInt(2,length);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                order = new Order();
                order.setOrderId(resultSet.getString("order_id"));
                order.setAddressId(resultSet.getInt("address_id"));
                order.setRid(resultSet.getInt("rid"));
                order.setSta(resultSet.getString("sta"));
                order.setUserId(resultSet.getString("user_id"));
                order.setPayment(resultSet.getDouble("payment"));
                order.setPlaced(resultSet.getTimestamp("placed"));
                order.setReceipt(resultSet.getTimestamp("receipt"));
                order.setDeliver(resultSet.getTimestamp("deliver"));
                order.setHandover(resultSet.getTimestamp("handover"));
                list.add(order);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }

    public ArrayList<Order> selectGot(int start, int length) {
        ArrayList<Order> list = new ArrayList<Order>();
        Order order = null;
        Connection connection = DBUtils.getConn();
        String sql = "select * from tb_order where sta='已交付' order by placed desc limit ?,?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,start);
            preparedStatement.setInt(2,length);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                order = new Order();
                order.setOrderId(resultSet.getString("order_id"));
                order.setAddressId(resultSet.getInt("address_id"));
                order.setRid(resultSet.getInt("rid"));
                order.setSta(resultSet.getString("sta"));
                order.setUserId(resultSet.getString("user_id"));
                order.setPayment(resultSet.getDouble("payment"));
                order.setPlaced(resultSet.getTimestamp("placed"));
                order.setReceipt(resultSet.getTimestamp("receipt"));
                order.setDeliver(resultSet.getTimestamp("deliver"));
                order.setHandover(resultSet.getTimestamp("handover"));
                list.add(order);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }

    public OrderDetailVo findAddressAndOrder(String orderId) {
        OrderDetailVo orderDetailVo = null;
        Order order = null;
        Address address = null;
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select address.*,tb_order.payment,tb_order.handover,tb_order.deliver," +
                "tb_order.order_id,tb_order.sta,tb_order.placed,tb_order.rid id " +
                "from tb_order inner join tb_address address " +
                "on tb_order.address_id=address.rid where tb_order.order_id=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,orderId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                order = new Order();
                order.setPayment(resultSet.getDouble("payment"));
                order.setOrderId(orderId);
                order.setSta(resultSet.getString("sta"));
                order.setPlaced(resultSet.getTimestamp("placed"));
                order.setRid(resultSet.getInt("id"));
                order.setHandover(resultSet.getTimestamp("handover"));
                order.setDeliver(resultSet.getTimestamp("deliver"));
                address = new Address();
                address.setReceiver(resultSet.getString("receiver"));
                address.setReceiverPhone(resultSet.getString("receiver_phone"));
                address.setAddress(resultSet.getString("address"));
                orderDetailVo = new OrderDetailVo();
                orderDetailVo.setAddress(address);
                orderDetailVo.setOrder(order);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
    return orderDetailVo;
    }

    public ArrayList<OrderWitnVo> findItemList(String orderId) {
        Book book = null;
        ArrayList<OrderWitnVo> list = new ArrayList<OrderWitnVo>();
        OrderItem orderItem = null;
        OrderWitnVo orderWitnVo = null;
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select book.title,item.* from tb_book book " +
                "inner join tb_order_item item on book.isbn=item.product " +
                "where item.order_id=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,orderId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                book = new Book();
                book.setTitle(resultSet.getString("title"));
                orderItem = new OrderItem();
                orderItem.setOrderId(orderId);
                orderItem.setProduct(resultSet.getString("product"));
                orderItem.setCount(resultSet.getInt("count"));
                orderItem.setPrice(resultSet.getDouble("price"));
                orderWitnVo = new OrderWitnVo();
                orderWitnVo.setBook(book);
                orderWitnVo.setOrderItem(orderItem);
                list.add(orderWitnVo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }

    public Integer updateStaById(String orderId, String sta) {
        Connection connection = DBUtils.getConn();
        String sql ="update tb_order set sta=? where order_id=?";
        PreparedStatement preparedStatement = null;
        Integer row = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sta);
            preparedStatement.setString(2,orderId);
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
    return row;
    }

    public Integer updateHandoverById(String orderId) {
        Connection connection = DBUtils.getConn();
        String sql ="update tb_order set handover=? where order_id=?";
        PreparedStatement preparedStatement = null;
        Integer row = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setTimestamp(1,new Timestamp(new Date().getTime()));
            preparedStatement.setString(2,orderId);
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
        return row;
    }

    public void updateDeliverById(String orderId) {
        Connection connection = DBUtils.getConn();
        String sql ="update tb_order set deliver=? where order_id=?";
        PreparedStatement preparedStatement = null;
        Integer row = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setTimestamp(1,new Timestamp(new Date().getTime()));
            preparedStatement.setString(2,orderId);
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
    }
}
